/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Database;

import Domain.SportFacility;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author Min0
 */
public class DBSportFacility implements IFDBSportFacility {
    
    private Connection con;

    public DBSportFacility()
    {
        con = DBConnection.getInstance().getDBcon();
    }

    //START private methods for class
    private SportFacility BuildSportFacility(ResultSet results)
    {
        SportFacility appObj = new SportFacility();
        try
        {
            appObj.setFacilityNo(results.getInt("FacilityNo"));
            appObj.setFacilityType(results.getString("FacilityType"));
            appObj.setBooked(Boolean.getBoolean(results.getString("Booked")));
            appObj.setCapacity(results.getInt("Capacity"));
        } 
        catch (Exception e)
        {
            System.out.println("error in building the SportFacility object\n" + e.getMessage());
        }
        return appObj;
    }

    private String BuildQuery(String wClause)
    {
        String query = "SELECT * FROM SportFacility";

        if (wClause.length() > 0)
        {
            query = query + " WHERE " + wClause;
        }

        return query;
    }

    private SportFacility singleWhere(String wClause)
    {
        ResultSet results;
        SportFacility appObj = new SportFacility();
        String query = BuildQuery(wClause);
        System.out.println(query);
        try
        {
            java.sql.Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if (results.next())
            {
                appObj = BuildSportFacility(results);
                //assocaition is to be build
                stmt.close();
            }

        }//end try	
        catch (Exception e)
        {
            System.out.println("Query exception: " + e);
        }
        return appObj;
    }

    private ArrayList<SportFacility> miscWhere(String wClause)
    {
        ResultSet results;
        ArrayList<SportFacility> list = new ArrayList<SportFacility>();

        String query = BuildQuery(wClause);

        try
        { // read the SportFacility from the database
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while (results.next())
            {
                SportFacility appObj = new SportFacility();
                appObj = BuildSportFacility(results);
                list.add(appObj);
            }//end while
            stmt.close();
        } catch (Exception e)
        {
            System.out.println("Query exception - select: " + e);
        }
        return list;
    }
    //END private methods for class

    @Override
    public void insertSportFacility(SportFacility SF)
    {
        String query = "INSERT INTO SportFacility (FacilityNo,FacilityType,Booked,Capacity) VALUES("
                + SF.getFacilityNo() + ",'"
                + SF.getFacilityType() + "','"
                + SF.isBooked() + "',"
                + SF.getCapacity() + ")";
        
        System.out.println("INSERT: " +query);
        
        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);
            stmt.close();
        }//end try	
        catch (Exception ex)
        {
            System.out.println("Insert exception in apartment db: " + ex);
        }
    }

    @Override
    public void insertSportFacility(int FacilityNo, String FacilityType, String Booked, int Capacity)
    {
        String query = "INSERT INTO SportFacility (FacilityNo,FacilityType,Booked,Capacity) VALUES("
                + FacilityNo + ",'"
                + FacilityType + "','"
                + Booked + "',"
                + Capacity + ")";
        
        System.out.println("INSERT: " +query);
        
        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);
            stmt.close();
        }//end try	
        catch (Exception ex)
        {
            System.out.println("Insert exception in apartment db: " + ex);
        }
    }

    @Override
    public SportFacility getSportFacility(int FacilityNo)
    {
        String wClause = "FacilityNo = " +FacilityNo;
        return singleWhere(wClause);
    }

    @Override
    public void updateSportFacility(int FacilityNo, String FacilityType, String Booked, int Capacity)
    {
        String query = "UPDATE SportFacility SET "
                + "FacilityType = '" + FacilityType + "',"
                + "Booked = '" + Booked + "',"
                + "Capacity = " + Capacity
                + " WHERE FacilityNo = " + FacilityNo;
        
        System.out.println("UPDATE QUERY: " +query);
        
        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);
            stmt.close();
        } catch (Exception ex)
        {
            System.out.println("Update exception in sportFacility db: " + ex);
        }
    }

    @Override
    public void deleteSportFacility(int FacilityNo)
    {
        String query = "DELETE FROM SportFacility WHERE FacilityNo = "
                + FacilityNo;
        System.out.println(query);
        try
        { // delete from apartment
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);
            stmt.close();
        } catch (Exception ex)
        {
            System.out.println("Delete exception in sportFacility db: " + ex);
        }
    }

    @Override
    public ArrayList<SportFacility> getSportFacilityList()
    {
        return miscWhere("");
    }

    @Override
    public int getMax()
    {
        int max = getMax.getMaxId("Select max(FacilityNo) from SportFacility");
        return max;
    }
}
